package com.san.oms.dao.score;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;

import com.san.common.datasource.JdbcManager;
import com.san.common.utils.Constant;
import com.san.common.utils.DateUtils;
import com.san.common.utils.StringUtils;
import com.san.oms.dao.ScoreManager;
import com.san.oms.domain.ModelScDTO;
import com.san.oms.domain.QuotaDO;

@Service
public class ScoreMysqlManager extends ScoreManager {

	@Override
	public boolean bakTable(ModelScDTO modelDTO) throws Exception {

		String infix = "";
		if (Constant.MODEL_TYPE_LEGAL_PERSON == modelDTO.getModelType()) {
			infix = "legal";
		} else if (Constant.MODEL_TYPE_NATURAL_PERSON == modelDTO.getModelType()) {
			infix = "natural";
		} else {
			return false;
		}

		String tableName = "oms_" + infix + "_score_" + modelDTO.getModelId();
		String newTableName = "oms_" + infix + "_score_" + modelDTO.getModelId() + "_" + DateUtils.getYesterday("MMdd");
		JdbcManager jdbcManager = new JdbcManager();
		// 删除索引1
		jdbcManager.executeSQL(this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE),
				"alter table " + tableName + " drop index " + tableName + "_01", null);
		// 删除索引2
		jdbcManager.executeSQL(this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE),
				"alter table " + tableName + " drop index " + tableName + "_02", null);

		// 更改表名
		jdbcManager.executeSQL(this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE),
				"alter table " + tableName + " rename " + newTableName, null);

		StringBuffer createSQL = new StringBuffer("create table ");
		createSQL.append(tableName);
		createSQL.append("(`score_id` bigint(20) AUTO_INCREMENT COMMENT '评分ID',");
		createSQL.append(infix + "_person_id varchar(50),");
		createSQL.append("`quota_id` bigint(20) COMMENT '模型指标ID',");
		createSQL.append("`score` varchar(10) COMMENT '分数',");
		createSQL.append("PRIMARY KEY (`score_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");

		// 创建表
		boolean bool = jdbcManager.executeSQL(this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE),
				createSQL.toString(), null);
		if (!bool) {
			// 创建表失败可能为更名失败，执行清空表操作
			jdbcManager.executeSQL(this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE), "truncate table " + tableName,
					null);
		}

		// 创建索引1
		jdbcManager.executeSQL(this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE),
				"create index " + tableName + "_01" + " on " + tableName + "(" + infix + "_person_id)", null);
		// 创建索引2
		jdbcManager.executeSQL(this.getDBConnection(Constant.SOURCE_CODE_BASESOURCE),
				"create index " + tableName + "_02" + " on " + tableName + "(" + infix + "_person_id,quota_id)", null);
		return true;
	}

	@Override
	public String getMaxValue(int modelType, QuotaDO quotaDO) throws Exception {
		String maxValue = null;
		// 表及字段中法人/自然人关键词
		String infix = this.getInfix(modelType);
		if (StringUtils.isEmpty(infix)) {
			return null;
		}
		String querySQL = "select max(CAST(busi_value AS SIGNED)) max_value from result_" + infix
				+ "_person where busi_id = ?";
		JdbcManager jdbcManager = new JdbcManager();
		List<Map<String, Object>> list = jdbcManager.executeQuery(this.getBaseDBConnection(), querySQL,
				new Object[] { quotaDO.getQuotaId() });
		for (Map<String, Object> map : list) {
			maxValue = String.valueOf(map.get("max_value"));
			break;
		}

		try {
			Long.parseLong(maxValue);
		} catch (Exception e) {
			return null;
		}

		return maxValue;
	}

	@Override
	public String getMinValue(int modelType, QuotaDO quotaDO) throws Exception {
		String minValue = null;
		// 表及字段中法人/自然人关键词
		String infix = this.getInfix(modelType);
		if (StringUtils.isEmpty(infix)) {
			return null;
		}
		String querySQL = "select min(CAST(busi_value AS SIGNED)) min_value from result_" + infix
				+ "_person where busi_id = ?";
		JdbcManager jdbcManager = new JdbcManager();
		List<Map<String, Object>> list = jdbcManager.executeQuery(this.getBaseDBConnection(), querySQL,
				new Object[] { quotaDO.getQuotaId() });
		for (Map<String, Object> map : list) {
			minValue = String.valueOf(map.get("min_value"));
			break;
		}

		try {
			Double.parseDouble(minValue);
		} catch (Exception e) {
			return null;
		}
		querySQL = null;
		return minValue;
	}

	@Override
	public void saveParentQuotaScore(ModelScDTO modelDTO, QuotaDO quotaDO, List<QuotaDO> childQuotaList)
			throws Exception {
		// 表及字段中法人/自然人关键词
		String infix = this.getInfix(modelDTO.getModelType());
		if (StringUtils.isEmpty(infix)) {
			return;
		}
		String tableName = "oms_" + infix + "_score_" + modelDTO.getModelId();

		StringBuffer insertSQL = new StringBuffer("insert into ");
		insertSQL.append(tableName).append(" (");
		insertSQL.append(infix).append("_person_id,quota_id,score)");
		insertSQL.append(" select ").append(infix).append("_person_id,");
		insertSQL.append(quotaDO.getQuotaId());
		if (quotaDO.getQuotaScore() >= 0) {
			insertSQL.append(",case when sum(cast(score AS DECIMAL)) > ").append(quotaDO.getQuotaScore()).append(" then ");
			insertSQL.append(quotaDO.getQuotaScore()).append(" else ").append(" sum(cast(score AS DECIMAL)) end");
		} else {
			insertSQL.append(",case when sum(cast(score AS DECIMAL)) < ").append(quotaDO.getQuotaScore()).append(" then ");
			insertSQL.append(quotaDO.getQuotaScore()).append(" else ").append(" sum(cast(score AS DECIMAL)) end");
		}
		insertSQL.append(" from ").append(tableName).append("  ");
		insertSQL.append(" where quota_id in (");
		for (int i = 0; i < childQuotaList.size(); i++) {
			QuotaDO childQuotaDO = childQuotaList.get(i);
			insertSQL.append(childQuotaDO.getQuotaId());
			if (i < childQuotaList.size() - 1) {
				insertSQL.append(",");
			}
		}
		insertSQL.append(") group by ").append(infix).append("_person_id");

		JdbcManager jdbcManager = new JdbcManager();
		jdbcManager.executeSQL(this.getBaseDBConnection(), insertSQL.toString(), null);
	}

}
